package com.jobanalysis.job_analysis.util;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

@Component
public class ExcelUtil {

    /**
     * 创建一个新的xls文件
     *
     * @param filePath
     * @param sheetName
     * @param dataMap
     */
    public static void create(String filePath, String sheetName, Map<Integer, Map<String, String>> dataMap) {

        //创建一个HSSFWorkbook，对应一个Excel文件
        Workbook wb = new XSSFWorkbook();
        FileOutputStream fileOutputStream;

        try {
            fileOutputStream = new FileOutputStream(filePath);
            //新建一个sheet对象
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(sheetName);
            //数据非空则插入
            if (dataMap != null) {
                insertData(sheet, dataMap);
                wb.write(fileOutputStream);
            }
            fileOutputStream.close();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }


    /**
     * 往sheet里面添加数据
     *
     * @param sheet
     * @param dataMap
     */
    private static void insertData(XSSFSheet sheet, Map<Integer, Map<String, String>> dataMap) {
        //用于判定当前遍历的行数
        int index = 0;
        for (Entry<Integer, Map<String, String>> dataEntry : dataMap.entrySet()) {

            //当index为0，添加表头
            if (index == 0) {
                //创建行
                XSSFRow row = sheet.createRow(index);
                Map<String, String> map = dataEntry.getValue();
                Integer rowKey = (Integer) dataEntry.getKey();


                int cellIndex = 0;
                for (Entry<String, String> entry : map.entrySet()) {
                    XSSFCell cell = row.createCell(cellIndex++);
                    cell.setCellValue(entry.getKey());
                }

            }
            //添加表数据
            XSSFRow row = sheet.createRow(++index);
            Map<String, String> map = dataEntry.getValue();

            int cellIndex = 0;
            for (Entry<String, String> entry : map.entrySet()) {
                XSSFCell cell = row.createCell(cellIndex++);
                cell.setCellValue(entry.getValue());
            }

        }


    }


    /**
     * 取出Excel的所有工作簿名
     *
     * @param xlsFilePath
     * @return
     */
    public static List<String> getSheets(String xlsFilePath) {
        List<String> result = new ArrayList<String>();

        try {
            FileInputStream fis = new FileInputStream(xlsFilePath);

            Workbook workbook = new XSSFWorkbook(fis);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                //获得工作簿表名
                String sheetName = workbook.getSheetName(i);
                result.add(i, sheetName);
            }
            fis.close();

        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return result;
    }

    /**
     * 取得工作簿中的所有行
     *
     * @param xlsFilePath
     * @param sheetName
     * @return
     */
    public static Map<Integer, Map<String, String>> getExcelData(String xlsFilePath, String sheetName) {
        Map<Integer, Map<String, String>> resultMap = new HashMap<Integer, Map<String, String>>();

        Map<String, String> resultCell;

        try {
            InputStream is = new FileInputStream(xlsFilePath);
            //POIFSFileSystem ts = new POIFSFileSystem(fis);
            //新建一个Excel的处理类
            resultMap = getMapDataByFileInputStream(is, sheetName);
            //关闭输入流
            is.close();

        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return resultMap;
    }

    /**
     * 根据文件输入流来获得Excel的数据(转换成Map)
     *
     * @param is
     * @return
     */
    public static Map<Integer, Map<String, String>>
    getMapDataByFileInputStream(InputStream is, String sheetName) throws IOException {
        Map<Integer, Map<String, String>> resultMap = new HashMap<Integer, Map<String, String>>();
        Map<String, String> resultCell;
        try {
            //换成你的文件名
            BufferedReader reader = new BufferedReader(new InputStreamReader(is));
            //reader.readLine();//第一行信息，为标题信息，不用,如果需要，注释掉
            String header[] = reader.readLine().split(",");
            reader.readLine();
            String line = null;
            int c = 0;
            while ((line = reader.readLine()) != null) {
                //if (c == 100) {
                //    break;
                //}
                resultCell = new HashMap<String, String>();
                String item[] = line.split(",");//CSV格式文件为逗号分隔符文件，这里根据逗号切分
                for (int m = 0; m < item.length; m++) {
                    //if (item[m] != null) {
                    String columnName = "";
                    if (m < header.length) {
                        columnName = header[m];
                    }

                    String cellValue = item[m];
                    resultCell.put(columnName, cellValue);
                    //}
                }
                resultMap.put(c, resultCell);
                c++;

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultMap;
    }

    /**
     * CSV文件生成方法
     *
     * @param head       文件头
     * @param dataList   数据列表
     * @param outPutPath 文件输出路径
     * @param filename   文件名
     * @return
     */
    public static File createCSVFile(List<Object> head, List<List<Object>> dataList, String outPutPath, String filename) {
        File csvFile = null;
        String originFilename = filename;
        String originPutPath = outPutPath;
        String[] name = new String[]{"count", "salary"};
        for (int i = 0; i < 2; i++) {

            filename = originFilename;
            filename += "_" + name[i];

            outPutPath = originPutPath;
            outPutPath += "\\" + originFilename;


            BufferedWriter csvWtriter = null;
            try {
                csvFile = new File(outPutPath + File.separator + filename + ".csv");
                if (csvFile.exists()) {
                    csvFile.delete();
                }
                File parent = csvFile.getParentFile();
                if (parent != null && !parent.exists()) {
                    parent.mkdirs();
                }
                csvFile.createNewFile();

                // GB2312使正确读取分隔符","
                csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
                        csvFile), "GB2312"), 1024);
                // 写入文件头部
                writeRow(head, csvWtriter, -1);

                // 写入文件内容
                for (List<Object> row : dataList) {
                    writeRow(row, csvWtriter, i + 1);
                }
                csvWtriter.flush();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    csvWtriter.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

            try {
                File file = ResourceUtils.getFile("src\\main\\resources");
                String abp = file.getAbsolutePath();

                //Runtime.getRuntime().exec("cd C:\\WINDOWS\\system32");
                Runtime runtime = Runtime.getRuntime();
                //runtime.exec("shutdown -s -t 3600");
                //System.out.println(abp.split(":")[0] + ":");
                //runtime.exec(abp.split(":")[0] + ":");
                //runtime.exec("cd " + file.getAbsolutePath(),null,new File(abp.split(":")[0] + ":"));
                File filecc = new File(file.getAbsolutePath() + "\\static\\images\\" + filename + ".png");
                if (filecc.exists()) {
                    filecc.delete();
                }
                runtime.exec("python main.py " + outPutPath + "\\" + filename + ".csv" + " " + filename, null, new File(file.getAbsolutePath()));

                //runtime.exec("python main.py " + outPutPath + "\\" + filename + ".csv" + " " + filename, null, new File(file.getAbsolutePath()));
                //exec.waitFor();
                //runtime.exec("ipconfig /flushdns");
                //Thread.sleep(1000);


            } catch (Exception e) {
                e.printStackTrace();
            }


        }
        return csvFile;
    }

    /**
     * 写一行数据方法
     *
     * @param row
     * @param csvWriter
     * @throws IOException
     */
    private static void writeRow(List<Object> row, BufferedWriter csvWriter, int index) throws IOException {

        if (index == -1) {
            // 写入文件头部
            for (Object data : row) {
                StringBuffer sb = new StringBuffer();
                String rowStr = sb.append("\"").append(data).append("\",").toString();
                csvWriter.write(rowStr);
            }
            csvWriter.newLine();
        } else {
            for (int i = 0; i < row.size(); i++) {
                if (i == 0 || i == index) {
                    StringBuffer sb = new StringBuffer();
                    String rowStr = sb.append("\"").append(row.get(i)).append("\",").toString();
                    csvWriter.write(rowStr);
                }
            }
            csvWriter.newLine();
        }

    }


    //@SuppressWarnings("static-access")
    //public static void main(String[] args) {
    //    List<Object> exportData = new ArrayList<Object>();
    //    exportData.add("第一列");
    //    exportData.add("第二列");
    //    exportData.add("第三列");
    //    List<List<Object>> datalist = new ArrayList<List<Object>>();
    //    List<Object> data=new ArrayList<Object>();
    //    data.add("111");
    //    data.add("222");
    //    data.add("333");
    //    List<Object> data1=new ArrayList<Object>();
    //    data1.add("444");
    //    data1.add("555");
    //    data1.add("666");
    //    datalist.add(data);
    //    datalist.add(data1);
    //    String path = "d:/exportCsv/";
    //    String fileName = "文件导出";
    //
    //    File file = new CompanyHourDataController().createCSVFile(exportData, datalist, path, fileName);
    //    String fileName2 = file.getName();
    //    System.out.println("文件名称：" + fileName2);
    //}


}
